"""
Case Type   : 数据库安全特性
Case Name   : 检测异常删除用户风险日志
Create At   : 2025/9
Owner       : l1azzzy
Description :
    1、设置enable_risky_query_detection为on
    2、创建测试用户
    3、执行删除用户语句
    4、检查数据库日志中风险检测记录
Expect      :
    1、参数设置成功
    2、创建用户成功
    3、删除用户语句执行完成
    4、数据库日志中存在risk关键字及相关删除用户SQL
History     :
"""

import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

class SecurityConfigCase0014(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.com = Common('PrimaryDbUser')
        self.comsh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log_path = self.get_log_directory()
        self.test_user = 'test_drop_user'
        self.pri_user = Node(node='PrimaryDbUser')

    def get_log_directory(self):
        """通过SQL查询获取日志目录路径"""
        self.log.info('-----获取日志目录路径-----')
        sql = "show log_directory;"
        
        res = self.comsh.execut_db_sql(sql)
        self.log.info(f"日志目录查询结果: {res}")
        
        lines = res.split('\n')
        for line in lines:
            line = line.strip()
            if not line or line.startswith(('log_directory', '---', '(', 'show ')):
                continue
            if '/' in line:
                self.log.info(f"提取到的日志路径: {line}")
                return line
    
        self.log.info(f"使用默认日志路径: {macro.PG_LOG_PATH}")
        return macro.PG_LOG_PATH
    
    def test_risky_drop_user_detection(self):
        text = '-----step1:设置enable_risky_query_detection为on-----'
        self.log.info(text)
        result = self.comsh.execute_gsguc('reload', 
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        'enable_risky_query_detection=on')
        self.assertTrue(result)

        text = '-----step2:创建测试用户-----'
        self.log.info(text)
        test_users = ['test_drop_user_001', 'test_drop_user_002', 'test_drop_user_003']
        
        for user in test_users:
            create_sql = f"create user {user} with password 'Test123456';"
            try:
                res = self.comsh.execut_db_sql(create_sql)
                self.log.info(f"创建用户 {user} 结果: {res}")
            except Exception as e:
                self.log.info(f"创建用户 {user} 异常: {e}")

        text = '-----step3:执行删除用户语句-----'
        self.log.info(text)
        risky_sqls = [
            "drop user test_drop_user_001;",
            "drop role test_drop_user_002;",
            "drop user if exists test_drop_user_003;",
            "drop role test_drop_user_001, test_drop_user_002;"
        ]
        
        for sql in risky_sqls:
            try:
                res = self.comsh.execut_db_sql(sql)
                self.log.info(f"SQL执行结果: {res}")
            except Exception as e:
                self.log.info(f"SQL执行异常: {e}")
        time.sleep(3)

        text = '-----step4:检查日志中风险检测记录-----'
        self.log.info(text)
        risk_found = False
        self.log.info("遍历所有日志文件查找...")
        cmd = f'find {self.log_path} -name "*.log" -type f'
        res = self.pri_user.sh(cmd).result()
        self.log.info(f"找到的日志文件数量: {len(res.splitlines())}")
        if res.strip():
            log_files = [f.strip() for f in res.split('\n') if f.strip()]
            for log_file in log_files:
                grep_cmd = f'grep -l "Risky.*drop user" {log_file} 2>/dev/null'
                grep_res = self.pri_user.sh(grep_cmd).result()
                if grep_res.strip():
                    self.log.info(f"在日志文件 {log_file} 中找到风险记录")
                    detail_cmd = f'grep -i "Risky.drop user" {log_file} | head -3'
                    detail_res = self.pri_user.sh(detail_cmd).result()
                    self.log.info(f"风险日志内容: {detail_res}")
                    risk_found = True

        self.assertTrue(risk_found, "未在日志中找到风险记录")

    def tearDown(self):
        text = '-----恢复enable_risky_query_detection为off-----'
        self.log.info(text)
        result = self.comsh.execute_gsguc('reload',
                            self.constant.GSGUC_SUCCESS_MSG,
                            'enable_risky_query_detection=off')
        cleanup_users = ['test_drop_user_001', 'test_drop_user_002', 'test_drop_user_003']
        for user in cleanup_users:
            cleanup_sql = f"drop user if exists {user};"
            try:
                self.comsh.execut_db_sql(cleanup_sql)
            except Exception as e:
                self.log.info(f"清理用户 {user} 异常: {e}")
        
        self.log.info(f'----{os.path.basename(__file__)} end----')